package com.example.dependencyscanner.dao;

import com.example.dependencyscanner.model.Vulnerability;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 漏洞数据访问层
 * 
 
 */
@Repository
public class VulnerabilityDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private static final RowMapper<Vulnerability> VULNERABILITY_ROW_MAPPER = new RowMapper<Vulnerability>() {
        @Override
        public Vulnerability mapRow(ResultSet rs, int rowNum) throws SQLException {
            Vulnerability vulnerability = new Vulnerability();
            vulnerability.setGroupId(rs.getString("group_id"));
            vulnerability.setArtifactId(rs.getString("artifact_id"));
            vulnerability.setVulnerableVersions(rs.getString("vulnerable_versions"));
            vulnerability.setSafeVersion(rs.getString("safe_version"));
            vulnerability.setCve(rs.getString("cve"));
            vulnerability.setDescription(rs.getString("description"));
            vulnerability.setSeverity(rs.getString("severity"));
            vulnerability.setReference(rs.getString("reference"));
            return vulnerability;
        }
    };
    
    /**
     * 获取所有漏洞信息
     */
    public List<Vulnerability> findAll() {
        String sql = "SELECT * FROM vulnerabilities ORDER BY severity DESC, group_id, artifact_id";
        return jdbcTemplate.query(sql, VULNERABILITY_ROW_MAPPER);
    }
    
    /**
     * 根据groupId和artifactId查找漏洞
     */
    public List<Vulnerability> findByGroupIdAndArtifactId(String groupId, String artifactId) {
        String sql = "SELECT * FROM vulnerabilities WHERE group_id = ? AND artifact_id = ?";
        return jdbcTemplate.query(sql, VULNERABILITY_ROW_MAPPER, groupId, artifactId);
    }
    
    /**
     * 插入漏洞信息
     */
    public int insert(Vulnerability vulnerability) {
        String sql = "INSERT INTO vulnerabilities (group_id, artifact_id, vulnerable_versions, " +
                    "safe_version, cve, description, severity, reference) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql,
                vulnerability.getGroupId(),
                vulnerability.getArtifactId(),
                vulnerability.getVulnerableVersions(),
                vulnerability.getSafeVersion(),
                vulnerability.getCve(),
                vulnerability.getDescription(),
                vulnerability.getSeverity(),
                vulnerability.getReference());
    }
    
    /**
     * 批量插入漏洞信息
     */
    public void batchInsert(List<Vulnerability> vulnerabilities) {
        String sql = "INSERT INTO vulnerabilities (group_id, artifact_id, vulnerable_versions, " +
                    "safe_version, cve, description, severity, reference) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?) " +
                    "ON DUPLICATE KEY UPDATE " +
                    "vulnerable_versions = VALUES(vulnerable_versions), " +
                    "safe_version = VALUES(safe_version), " +
                    "description = VALUES(description), " +
                    "severity = VALUES(severity), " +
                    "reference = VALUES(reference), " +
                    "updated_at = CURRENT_TIMESTAMP";
        
        jdbcTemplate.batchUpdate(sql, vulnerabilities, vulnerabilities.size(),
                (ps, vulnerability) -> {
                    ps.setString(1, vulnerability.getGroupId());
                    ps.setString(2, vulnerability.getArtifactId());
                    ps.setString(3, vulnerability.getVulnerableVersions());
                    ps.setString(4, vulnerability.getSafeVersion());
                    ps.setString(5, vulnerability.getCve());
                    ps.setString(6, vulnerability.getDescription());
                    ps.setString(7, vulnerability.getSeverity());
                    ps.setString(8, vulnerability.getReference());
                });
    }
    
    /**
     * 清空漏洞数据
     */
    public void deleteAll() {
        jdbcTemplate.update("DELETE FROM vulnerabilities");
    }
    
    /**
     * 获取漏洞总数
     */
    public int count() {
        return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM vulnerabilities", Integer.class);
    }
}